SELECT 
	D.TRAC_ID,
	D.PRODUCT, 
	D.FEATURE, 
	COALESCE(E.CNT, 0) MTD_CNT, 
	COALESCE(J.CNT, 0) PREV_MTD_CNT, 
	COALESCE(H.CNT, 0) PREV_MON_CNT, 
	MTD_CNT * G.LAST_DAY / F.DOM PACING,
	CASE WHEN PREV_MON_CNT=0 THEN 0 ELSE CAST(PACING AS DECIMAL(7,4))/PREV_MON_CNT END PACE_RATE,
	'MTD_SALES' ROLLUP_TYPE	

FROM 
(	
	SELECT P.PRODUCT, P.FEATURE, TRAC_ID 
	FROM MIS.ACC_RAW A 
	RIGHT OUTER JOIN
	(	
		SELECT DISTINCT product, feature  FROM MIS.ACC_CHANNEL
	)	P
	ON P.PRODUCT = A.PRODUCT AND A.FEATURE = P.FEATURE 
	GROUP BY 1,2,3 
) 	D

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
	FROM 
		MIS.ACC_RAW A,

		(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE  ) C
	WHERE A.START_DT BETWEEN C.BOM AND C.EOM  
	 GROUP BY 1, 2, 3
)  E 
ON D.PRODUCT=E.PRODUCT AND D.FEATURE=E.FEATURE AND D.TRAC_ID=E.TRAC_ID

LEFT OUTER JOIN 
(	
 	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  	FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT CALENDAR_DATE-DAY_OF_MONTH+1 BOM, CALENDAR_DATE EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=ADD_MONTHS(CURRENT_DATE,-1)		) C
  	WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  	GROUP BY 1,2,3
)  J 
ON D.PRODUCT=J.PRODUCT AND D.FEATURE=J.FEATURE AND D.TRAC_ID=J.TRAC_ID

LEFT OUTER JOIN 
 	(	SELECT PRODUCT, FEATURE,  COALESCE(A.TRAC_ID,'') TRAC_ID, COUNT(*) CNT
  		FROM 
  			MIS.ACC_RAW A,
			
  			(	SELECT ADD_MONTHS(CALENDAR_DATE-DAY_OF_MONTH+1,-1) BOM, CALENDAR_DATE-DAY_OF_MONTH EOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	) C
  		WHERE A.START_DT BETWEEN C.BOM AND C.EOM 
  		GROUP BY 1,2,3
  	) H 
ON D.PRODUCT=H.PRODUCT AND D.FEATURE=H.FEATURE AND D.TRAC_ID=H.TRAC_ID ,

(	
 	SELECT DAY_OF_MONTH DOM FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE	
) 	F,

(	
 	SELECT MAX(DAY_OF_MONTH) LAST_DAY FROM SYS_CALENDAR.CALENDAR 	WHERE MONTH_OF_CALENDAR=
 	(	SELECT MONTH_OF_CALENDAR FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)	
) 	G
UNION
SELECT
	'' TRAC_ID,
	'' PRODUCT, 
	'' FEATURE, 
	0 MTD_CNT, 
	0 PREV_MTD_CNT, 
	0 PREV_MON_CNT, 
	1 PACING,
	0 PACE_RATE,
	'MTD_SALES'	 ROLLUP_TYPE
FROM 
	MIS.ACC_RAW 
ORDER BY 1,2,3